ALTER VIEW [dbo].[vw_Test_Wip_View] AS(
SELECT  
	LT.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER,FAB_LOT_NUMBER, ISNULL(LOT_CATEGORY,'PROD') AS LOT_CATEGORY, 
	QTY_IN, DATE_IN,STAGE_NAME, STAGE_POSITION,LOT_CURRENT_STATUS,FTL_EXPECTED_DATE, PART_NUMBER,
	(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
		CASE 
			WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
		END 
	) AS BASE_PRODUCT,
	(CASE 
		WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
		WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
	END) AS BASE_PRODUCT_ID ,PS_TYPE_NAME, PS_TYPE,	6 AS PS_TYPE_ID, LT.LOT_ID, LT.LOCATION_ID,
	/*(CASE
		WHEN ( PARAMETER_VALUE IS NULL OR PARAMETER_VALUE = '' ) THEN 'TCP'
		ELSE
		PARAMETER_VALUE
	END)*/ NULL AS PACKAGE_TYPE
FROM 
	LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS,LOCATION L,FINAL_TEST_LOT FTL
WHERE 
	LT.LOT_ID = FTL.LOT_ID
	AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
	AND LT.LOCATION_ID = L.LOCATION_ID
	AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
	AND QTY_IN > 0 
	AND ( LOT_CURRENT_STATUS = 'Running' OR LOT_CURRENT_STATUS = 'Hold' )
	AND P.OBSOLETE != 0
	AND PS_TYPE_NAME = 'TEST' 
)
